package com.zb.orm;

import java.io.Serializable;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.inject.Inject;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import com.zb.orm.meta.EntityColumnMetadata;
import com.zb.orm.meta.EntityMetadata;
import com.zb.orm.query.OrderBy;
import com.zb.orm.query.QueryContext;
import com.zb.orm.query.QueryContextPiece;
import com.zb.orm.query.operator.BETWEEN;
import com.zb.orm.query.operator.IN;
import com.zb.orm.query.operator.NOT_BETWEEN;
import com.zb.orm.query.operator.NOT_IN;
import com.zb.util.ArrayUtil;
import com.zb.util.CollectionUtil;
import com.zb.util.IdGenerator;
import com.zb.vo.PageVo;

/**
 * 基础DAO接口实现
 * 
 * 作者: zhoubang 日期：2015年3月26日 下午1:24:57
 * 
 * @param <T>
 */
@SuppressWarnings("unchecked")
public class AbstractDaoSupportImpl<T> implements DaoSupport<T> {

    private static transient final Logger LOGGER = LoggerFactory.getLogger(AbstractDaoSupportImpl.class);

    protected static transient final String CONST_EQ = "=";
    protected static transient final String CONST_AND = " and ";
    protected static transient final String CONST_OR = " or ";
    protected static transient final String CONST_WHERE = " where ";
    protected static transient final String CONST_FROM = " from ";
    protected static transient final String CONST_SELECT = "select ";
    protected static transient final String CONST_UPDATE = "update ";
    protected static transient final String CONST_COUNT = " count(*) ";
    protected static transient final String CONST_ONE = " 1=1 ";
    protected static transient final String CONST_ORDER_BY = " order by ";
    protected static transient final String CONST_ORDER_BY_ASC = " asc ";
    protected static transient final String CONST_ORDER_BY_DESC = " desc ";
    protected static transient final String CONST_SPACE = " ";
    protected static transient final String CONST_DELETE = "delete ";

    protected EntityMetadata<T> metadata;

    protected String selectHead;

    protected String updateHead;

    protected String updateHeadNoPK;

    protected String allFields;

    protected String allColumns;

    protected String allFieldsNoPK;

    protected String allColumnsNoPK;

    @Inject
    protected JdbcTemplate jdbcTemplate;

    @Inject
    protected NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private RowMapper<T> rowMapper;

    /**
     * Constructor for AbstractDaoSupportImpl.
     */
    public AbstractDaoSupportImpl() {
        super();
        this.metadata = new EntityMetadata<T>(this.getEntityClass());
        StringBuffer sb = null;
        List<String> tmpArr = new ArrayList<String>();
        for (EntityColumnMetadata foo : this.metadata.getAllColumnMetadatas()) {
            sb = new StringBuffer();
            tmpArr.add(sb.append("`").append(foo.getName()).append("` as `").append(foo.getField()).append("`").toString());
        }
        this.selectHead = CollectionUtil.join(tmpArr, ",");

        List<String> liWithPK = new ArrayList<String>();
        List<String> liWithOutPK = new ArrayList<String>();
        for (EntityColumnMetadata col : this.metadata.getAllColumnMetadatas()) {
            sb = new StringBuffer();
            sb.append("`").append(col.getName()).append("`=:").append(col.getField()).toString();
            liWithPK.add(sb.toString());
            if (!col.isPrimaryKey()) {
                liWithOutPK.add(sb.toString());
            }
        }
        this.updateHead = CollectionUtil.join(liWithPK, ",");
        this.updateHeadNoPK = CollectionUtil.join(liWithOutPK, ",");
        this.allFields = CollectionUtil.join(this.metadata.getAllFields(), ",");
        this.allColumns = "`" + CollectionUtil.join(this.metadata.getAllColumnNames(), "`,`") + "`";
        this.allColumnsNoPK = "`" + CollectionUtil.join(this.metadata.getColumnNamesNoPrimaryKey(), "`,`") + "`";
        this.allFieldsNoPK = CollectionUtil.join(this.metadata.getFieldsNoPrimaryKey(), ",");

        LOGGER.debug(MessageFormat.format("+++++ Initialize DaoSupport for class[{0}] SUCCESS! +++++", this.getEntityClass().getCanonicalName()));
    }

    protected RowMapper<T> createRowMapper() {
        if (this.rowMapper == null) {
            this.rowMapper = new BeanPropertyRowMapper<T>((Class<T>) this.metadata.getClazz());
        }
        return this.rowMapper;
    }

    protected SqlParameterSource createSqlParameterSource(T t) {
        return new BeanPropertySqlParameterSource(t);
    }

    @Override
    public Class<T> getEntityClass() {
        if (this.metadata == null) {
            return (Class<T>) this.getGenericType(0);
        }
        return this.metadata.getClazz();
    }

    /**
     * 获取当前类的泛型类型
     * 
     * @param index
     * @return
     */
    private Class<?> getGenericType(int index) {
        Type genType = this.getClass().getGenericSuperclass();
        if (!(genType instanceof ParameterizedType)) {
            return Object.class;
        }
        Type[] params = ((ParameterizedType) genType).getActualTypeArguments();
        if (index >= params.length || index < 0) {
            throw new RuntimeException("Index outof bounds");
        }
        if (!(params[index] instanceof Class)) {
            return Object.class;
        }
        return (Class<?>) params[index];
    }

    @Override
    public JdbcTemplate getJdbcTemplate() throws Exception {
        return this.jdbcTemplate;
    }

    @Override
    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate()
            throws Exception {
        return this.namedParameterJdbcTemplate;
    }

    @Override
    public long count(Map<String, Object> map) throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append(CONST_SELECT).append(CONST_COUNT).append(CONST_FROM).append(this.metadata.getQualifiedTableName()).append(CONST_WHERE).append(CONST_ONE);

        if (MapUtils.isNotEmpty(map)) {
            for (Iterator<Entry<String, Object>> it = map.entrySet().iterator(); it.hasNext();) {
                Entry<String, Object> entry = (Entry<String, Object>) it.next();
                if (StringUtils.isBlank(entry.getKey()) || entry.getValue() == null) {
                    continue;
                }
                sb.append(CONST_AND).append(this.metadata.getColumnByField(entry.getKey()).getNameWithQuote());
                sb.append(CONST_EQ).append(":").append(entry.getKey());
            }
        }
        final String sql = sb.toString();
        try {
            return this.namedParameterJdbcTemplate.queryForObject(sql, map, Long.class);
        } catch (Throwable e) {
            throw new Exception(e);
        }
    }

    @Override
    public long count(String field, Object value) throws Exception {
        final Map<String, Object> map = new HashMap<String, Object>(1);
        map.put(field, value);
        return this.count(map);
    }

    @Override
    public long count(String[] fields, Object[] values) throws Exception {
        final boolean isArrNotEmpty = ArrayUtils.isNotEmpty(fields) && ArrayUtils.isNotEmpty(values);
        Map<String, Object> map = null;
        if (isArrNotEmpty) {
            if (fields.length != values.length) {
                throw new Exception("length of field array[" + fields.length + "] doesn't equal with length of value array[" + values.length + "].");
            } else {
                map = new HashMap<String, Object>();
                for (int i = 0; i < fields.length; i++) {
                    map.put(fields[i], values[i]);
                }
            }
        }
        return this.count(map);
    }

    @Override
    public long count(QueryContext context) throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append(CONST_SELECT).append("count(*)").append(CONST_FROM).append(this.metadata.getQualifiedTableName()).append(CONST_WHERE).append(CONST_ONE);
        
        List<Object> params = new ArrayList<Object>();
        this.parseQueryContext(sb, context, params);

        final String sql = sb.toString();
        try {
            return this.jdbcTemplate.queryForObject(sql, ArrayUtil.toArray(params, Object.class), Long.class);
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    @Override
    public long countAll() throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append(CONST_SELECT).append(CONST_COUNT).append(CONST_FROM).append(this.metadata.getQualifiedTableName());
        try {
            return this.jdbcTemplate.queryForObject(sb.toString(), Long.class);
        } catch (Throwable e) {
            throw new Exception(e);
        }
    }

    @Override
    public void delete(Serializable primaryfield) throws Exception {
        if (primaryfield == null) {
            throw new Exception("cannot delete entity: primary value is null.");
        }
        StringBuffer sb = new StringBuffer(CONST_DELETE);
        sb.append(CONST_FROM).append(this.metadata.getQualifiedTableName()).append(CONST_WHERE).append(this.metadata.getPrimaryKey().getName()).append(CONST_EQ).append("?");
        
        final String sql = sb.toString();
        try {
            this.jdbcTemplate.update(sql, primaryfield);
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    @Override
    public void delete(String field, Object value) throws Exception {
        this.delete(new String[] { field }, new Object[] { value });
    }

    @Override
    public void delete(String[] fields, Object[] values) throws Exception {
        if (ArrayUtils.isEmpty(fields) || ArrayUtils.isEmpty(values) || fields.length != values.length) {
            throw new Exception("fiels and values is empty or length doesn't match.");
        }

        StringBuffer sb = new StringBuffer(CONST_DELETE);
        sb.append(CONST_FROM).append(this.metadata.getQualifiedTableName()).append(CONST_WHERE).append(CONST_ONE);

        for (int i = 0, len = fields.length; i < len; i++) {
            sb.append(CONST_AND).append(this.metadata.getColumnByField(fields[i]).getNameWithQuote()).append(CONST_EQ).append("?");
        }

        final String sql = sb.toString();
        try {
            this.jdbcTemplate.update(sql, values);
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    @Override
    public void delete(Map<String, Object> map) throws Exception {
        if (MapUtils.isEmpty(map)) {
            LOGGER.debug("delete by map cancel: map is empty.");
            return;
        }
        int len = map.size();

        String[] fields = new String[len];
        Object[] values = new Object[len];
        int i = 0;
        for (Iterator<Entry<String, Object>> it = map.entrySet().iterator(); it.hasNext(); i++) {
            Entry<String, Object> entry = it.next();
            fields[i] = entry.getKey();
            values[i] = entry.getValue();
        }
        this.delete(fields, values);
    }

    @Override
    public void delete(QueryContext queryContext) throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append(CONST_DELETE).append(CONST_FROM).append(this.metadata.getQualifiedTableName()).append(CONST_WHERE).append(CONST_ONE);
        
        List<Object> params = new ArrayList<Object>();
        this.parseQueryContext(sb, queryContext, params);

        if (CollectionUtils.isEmpty(params)) {
            LOGGER.debug("delete by query context cancel: querycontext is empty.");
            return;
        }
        this.jdbcTemplate.update(sb.toString(), ArrayUtil.toArray(params, Object.class));
    }

    @Override
    public void delete(T t) throws Exception {
        try {
            Serializable value = this.getPrimaryValue(t);
            this.delete(value);
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    @Override
    public void deleteAll() throws Exception {
        StringBuffer sb = new StringBuffer(CONST_DELETE).append(CONST_FROM).append(this.metadata.getQualifiedTableName());
        this.jdbcTemplate.update(sb.toString());
    }

    @Override
    public void executeSQL(String sql, Object... params) throws Exception {
        if (StringUtils.strip(sql).toLowerCase().startsWith("select")) {
            return;
        }
        this.jdbcTemplate.update(sql, params);
    }

    @Override
    public T load(Serializable pkValue) throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append(CONST_SELECT).append(this.selectHead).append(CONST_FROM).append(this.metadata.getQualifiedTableName());
        sb.append(CONST_WHERE).append(this.metadata.getPrimaryKey().getName()).append(CONST_EQ).append("? limit 0,1");
        final String sql = sb.toString();
        List<T> li = this.jdbcTemplate.query(sql, new Object[] { pkValue }, createRowMapper());
        if (CollectionUtils.isNotEmpty(li)) {
            return li.get(0);
        }
        return null;
    }

    @Override
    public List<T> query(Map<String, Object> map, OrderBy... orderBy)
            throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append(CONST_SELECT).append(this.selectHead).append(CONST_FROM).append(this.metadata.getQualifiedTableName()).append(CONST_WHERE).append(CONST_ONE);
        if (MapUtils.isNotEmpty(map)) {
            EntityColumnMetadata foo = null;
            for (Iterator<Entry<String, Object>> it = map.entrySet().iterator(); it.hasNext();) {
                Entry<String, Object> entry = it.next();
                foo = this.metadata.getColumnByField(entry.getKey());
                sb.append(CONST_AND).append(foo.getName()).append("=:").append(foo.getField());
            }
        }

        this.parseOrder(sb, orderBy);

        final String sql = sb.toString();
        try {
            List<T> li = this.namedParameterJdbcTemplate.query(sql, MapUtils.isNotEmpty(map) ? map : new HashMap<String, Object>(), createRowMapper());
            return li;
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    @Override
    public List<T> query(String field, Object value, OrderBy... orderBy)
            throws Exception {
        final Map<String, Object> map = new HashMap<String, Object>(1);
        map.put(field, value);
        return this.query(map, orderBy);
    }

    @Override
    public List<T> query(String[] fields, Object[] values, OrderBy... orderBy)
            throws Exception {
        if (fields == null || values == null) {
            throw new Exception("query fields and values array cannot be null.");
        }

        if (fields.length != values.length) {
            throw new Exception("fields.length != values.length");
        }
        final Map<String, Object> map = new HashMap<String, Object>(fields.length);
        for (int i = 0; i < fields.length; i++) {
            map.put(fields[i], values[i]);
        }
        return query(map, orderBy);
    }

    private List<T> query(QueryContext context, int limitStart, int limitSize,
            OrderBy... orderBy) throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append(CONST_SELECT).append(this.selectHead).append(CONST_FROM).append(this.metadata.getQualifiedTableName()).append(CONST_WHERE).append(CONST_ONE);

        final List<Object> params = new ArrayList<Object>();
        this.parseQueryContext(sb, context, params);
        this.parseOrder(sb, orderBy);

        if (limitSize > 0 && limitStart > -1) {
            sb.append(" limit ").append(limitStart).append(",").append(limitSize);
        }

        final String sql = sb.toString();
        try {
            return this.jdbcTemplate.query(sql, ArrayUtil.toArray(params, Object.class), createRowMapper());
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception();
        }
    }

    @Override
    public List<T> query(QueryContext context, OrderBy... orderBy)
            throws Exception {
        return this.query(context, -1, -1, orderBy);
    }

    @Override
    public List<T> queryAll(OrderBy... orderBy) throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append(CONST_SELECT).append(this.selectHead).append(CONST_FROM).append(this.metadata.getQualifiedTableName());
        this.parseOrder(sb, orderBy);
        final String sql = sb.toString();
        try {
            return this.jdbcTemplate.query(sql, createRowMapper());
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    @Override
    public List<T> queryBySQL(String sql, Object... params) throws Exception {
        try {
            return this.jdbcTemplate.query(sql, params, createRowMapper());
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    @Override
    public T queryBySQLFirst(String sql, Object... params) throws Exception {
        List<T> li = this.queryBySQL(sql + " limit 0,1", params);
        return CollectionUtils.isNotEmpty(li) ? li.get(0) : null;
    }

    @Override
    public T queryFirst(Map<String, Object> map, OrderBy... orderBy)
            throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append(CONST_SELECT).append(this.selectHead).append(CONST_FROM).append(this.metadata.getQualifiedTableName()).append(CONST_WHERE).append(CONST_ONE);
        
        if (MapUtils.isNotEmpty(map)) {
            for (Iterator<Entry<String, Object>> it = map.entrySet().iterator(); it.hasNext();) {
                Entry<String, Object> entry = it.next();
                EntityColumnMetadata col = this.metadata.getColumnByField(entry.getKey());
                sb.append(CONST_AND).append(col.getName()).append(CONST_EQ).append(":").append(col.getField());
            }
        }

        this.parseOrder(sb, orderBy);

        final String sql = sb.toString();
        final List<T> li = this.namedParameterJdbcTemplate.query(sql, map, createRowMapper());
        return CollectionUtils.isNotEmpty(li) ? li.get(0) : null;
    }

    @Override
    public T queryFirst(String field, Object value, OrderBy... orderBy)
            throws Exception {
        final Map<String, Object> map = new HashMap<String, Object>(1);
        map.put(field, value);
        return this.queryFirst(map, orderBy);
    }

    @Override
    public T queryFirst(String[] fields, Object[] values, OrderBy... orderBy)
            throws Exception {
        if (fields == null || values == null) {
            throw new Exception("query fields and values cannot be null.");
        }
        if (fields.length != values.length) {
            throw new Exception("fields.length doesn't equal with values.length.");
        }
        final Map<String, Object> map = new HashMap<String, Object>(fields.length);
        for (int i = 0; i < fields.length; i++) {
            map.put(fields[i], values[i]);
        }
        return this.queryFirst(map, orderBy);
    }

    @Override
    public T queryFirst(QueryContext context, OrderBy... orderBy)
            throws Exception {
        List<T> li = this.query(context, 0, 1, orderBy);
        return CollectionUtils.isNotEmpty(li) ? li.get(0) : null;
    }

    @Override
    public List<T> queryLimit(int limitFrom, int limitSize, OrderBy... orderBy)
            throws Exception {
        return query(null, limitFrom, limitSize, orderBy);
    }

    @Override
    public List<T> queryLimit(String field, Object value, int limitFrom,
            int limitSize, OrderBy... orderBy) throws Exception {
        final Map<String, Object> map = new HashMap<String, Object>(1);
        map.put(field, value);
        return this.queryLimit(map, limitFrom, limitSize, orderBy);
    }

    @Override
    public List<T> queryLimit(String[] fields, Object[] values, int limitFrom,
            int limitSize, OrderBy... orderBy) throws Exception {
        if (fields == null || values == null) {
            throw new Exception("query fields and values cannot be null.");
        }
        if (fields.length != values.length) {
            throw new Exception("fields.length doesn't equal with values.length.");
        }
        final Map<String, Object> map = new HashMap<String, Object>(fields.length);
        for (int i = 0; i < fields.length; i++) {
            map.put(fields[i], values[i]);
        }
        return this.queryLimit(map, limitFrom, limitSize, orderBy);
    }

    @Override
    public List<T> queryLimit(Map<String, Object> map, int limitFrom,
            int limitSize, OrderBy... orderBy) throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append(CONST_SELECT).append(this.selectHead).append(CONST_FROM).append(this.metadata.getQualifiedTableName()).append(CONST_WHERE).append(CONST_ONE);
        if (MapUtils.isNotEmpty(map)) {
            for (Iterator<Entry<String, Object>> it = map.entrySet().iterator(); it.hasNext();) {
                Entry<String, Object> entry = it.next();
                if (StringUtils.isBlank(entry.getKey()) || entry.getValue() == null) {
                    continue;
                }
                EntityColumnMetadata col = this.metadata.getColumnByField(entry.getKey());
                sb.append(CONST_AND).append(col.getName()).append(CONST_EQ).append(":").append(col.getField());
            }
        }

        this.parseOrder(sb, orderBy);
        sb.append(CONST_SPACE).append("limit ").append(limitFrom).append(",").append(limitSize);
        final String sql = sb.toString();
        try {
            List<T> li = this.namedParameterJdbcTemplate.query(sql, map, createRowMapper());
            return li;
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    @Override
    public List<T> queryLimit(QueryContext context, int limitFrom,
            int limitSize, OrderBy... orderBy) throws Exception {
        if (limitFrom < 0 || limitSize < 1) {
            throw new IllegalArgumentException("illegal limit params.");
        }
        StringBuffer sb = new StringBuffer();
        sb.append(CONST_SELECT).append(this.selectHead).append(CONST_FROM).append(this.metadata.getQualifiedTableName()).append(CONST_WHERE).append(CONST_ONE);
        List<Object> params = new ArrayList<Object>();
        this.parseQueryContext(sb, context, params);
        parseOrder(sb, orderBy);

        sb.append(" limit ").append(limitFrom).append(',').append(limitSize);
        final String sql = sb.toString();
        return this.jdbcTemplate.query(sql, ArrayUtil.toArray(params, Object.class), createRowMapper());

    }

    @Override
    public PageVo<T> queryWithPage(int pageNo, int pageSize, OrderBy... orderBy)
            throws Exception {
        return this.queryWithPage(pageNo, pageSize, new HashMap<String, Object>(), orderBy);
    }

    @Override
    public PageVo<T> queryWithPage(int pageNo, int pageSize, String field,
            Object value, OrderBy... orderBy) throws Exception {
        final Map<String, Object> paramMap = new HashMap<String, Object>(1);
        paramMap.put(field, value);
        return this.queryWithPage(pageNo, pageSize, paramMap, orderBy);
    }

    @Override
    public PageVo<T> queryWithPage(int pageNo, int pageSize, String[] fields,
            Object[] values, OrderBy... orderBy) throws Exception {
        if (fields == null || values == null) {
            throw new IllegalArgumentException("fields or values is null.");
        }
        if (fields.length != values.length) {
            throw new IllegalArgumentException("length of fields and values doesn't match.");
        }
        final Map<String, Object> paramMap = new HashMap<String, Object>(fields.length);
        for (int i = 0; i < fields.length; i++) {
            paramMap.put(fields[i], values[i]);
        }
        return this.queryWithPage(pageNo, pageSize, paramMap, orderBy);
    }

    @Override
    public PageVo<T> queryWithPage(int pageNo, int pageSize,
            Map<String, Object> paramMap, OrderBy... orderBy) throws Exception {
        try {
            List<T> li = this.queryLimit(paramMap, (pageNo - 1) * pageSize, pageSize, orderBy);
            long count = this.count(paramMap);
            return new PageVo<T>(li, count, pageNo, pageSize);
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    @Override
    public PageVo<T> queryWithPage(int pageNo, int pageSize,
            QueryContext context, OrderBy... orderBy) throws Exception {
        try {
            List<T> li = this.queryLimit(context, (pageNo - 1) * pageSize, pageSize, orderBy);
            long count = this.count(context);
            return new PageVo<T>(li, count, pageNo, pageSize);
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }

    @Override
    public void save(T... t) throws Exception {
        for (T t2 : t) {
            // 自定义生成ID
            final StringBuffer sb = new StringBuffer();
            sb.append("insert into ");
            sb.append(this.metadata.getQualifiedTableName()).append("(");
            sb.append(this.allColumns).append(") values(:");
            sb.append(CollectionUtil.join(this.metadata.getAllFields(), ",:")).append(")");
            final String sql = sb.toString();
            Class<?> tmpClazz = this.metadata.getPrimaryKey().getFieldType();
            Method setMethod = null;
            try {
                setMethod = this.metadata.getClazz().getMethod("set" + StringUtils.capitalize(this.metadata.getPrimaryKey().getField()), tmpClazz);
                setMethod.invoke(t2, IdGenerator.get());
            } catch (Throwable e) {
                e.printStackTrace();
                throw new Exception(e);
            }
            this.namedParameterJdbcTemplate.update(sql, createSqlParameterSource(t2));

            // //自增长ID获取
            // final StringBuffer sb = new StringBuffer();
            // sb.append("insert into ");
            // sb.append(this.metadata.getQualifiedTableName()).append("(");
            // sb.append(this.allColumnsNoPK).append(") values(:");
            // sb.append(CollectionUtil.join(this.metadata.getFieldsNoPrimaryKey(),
            // ",:")).append(")");
            // final String sql = sb.toString();
            // KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
            // this.namedParameterJdbcTemplate.update(sql,
            // createSqlParameterSource(t2), generatedKeyHolder);
            // Class<?> tmpClazz = this.metadata.getPrimaryKey().getFieldType();
            // Number genKey = generatedKeyHolder.getKey();
            // Method setMethod = null;
            // try {
            // setMethod = this.metadata.getClazz().getMethod(
            // "set" +
            // StringUtils.capitalize(this.metadata.getPrimaryKey().getField()),
            // tmpClazz);
            // if (tmpClazz.equals(Long.class) || tmpClazz.equals(long.class)) {
            // setMethod.invoke(t2, genKey.longValue());
            // } else if (tmpClazz.equals(Integer.class) ||
            // tmpClazz.equals(int.class)) {
            // setMethod.invoke(t2, genKey.intValue());
            // } else if (tmpClazz.equals(Short.class) ||
            // tmpClazz.equals(short.class)) {
            // setMethod.invoke(t2, genKey.shortValue());
            // } else if (tmpClazz.equals(Float.class) ||
            // tmpClazz.equals(float.class)) {
            // setMethod.invoke(t2, genKey.floatValue());
            // } else if (tmpClazz.equals(Double.class) ||
            // tmpClazz.equals(double.class)) {
            // setMethod.invoke(t2, genKey.doubleValue());
            // } else {
            // throw new
            // UnsupportedOperationException("unsupported key holder type[" +
            // tmpClazz + "].");
            // }
            // } catch (Throwable e) {
            // e.printStackTrace();
            // throw new Exception(e);
            // }
        }
    }

    @Override
    public void batchSave(List<T> t) throws Exception {
        // 自定义生成ID
        final StringBuffer sb = new StringBuffer();
        sb.append("insert into ");
        sb.append(this.metadata.getQualifiedTableName()).append("(");
        sb.append(this.allColumns).append(") values(:");
        sb.append(CollectionUtil.join(this.metadata.getAllFields(), ",:")).append(")");
        final String sql = sb.toString();

        List<SqlParameterSource> batchArgs = new ArrayList<SqlParameterSource>();
        for (T t2 : t) {
            Class<?> tmpClazz = this.metadata.getPrimaryKey().getFieldType();
            Method setMethod = null;
            try {
                setMethod = this.metadata.getClazz().getMethod("set" + StringUtils.capitalize(this.metadata.getPrimaryKey().getField()), tmpClazz);
                setMethod.invoke(t2, IdGenerator.get());
                batchArgs.add(createSqlParameterSource(t2));
            } catch (Throwable e) {
                e.printStackTrace();
                throw new Exception(e);
            }
        }
        this.namedParameterJdbcTemplate.batchUpdate(sql, ArrayUtil.toArray(batchArgs, SqlParameterSource.class));
    }

    @Override
    public void update(T... t) throws Exception {
        for (T t2 : t) {
            final StringBuffer sb = new StringBuffer("update ");
            sb.append(this.metadata.getQualifiedTableName()).append(" set ").append(this.updateHeadNoPK);
            sb.append(CONST_WHERE).append(this.metadata.getPrimaryKey().getName()).append("=:").append(this.metadata.getPrimaryKey().getField());
            final String sql = sb.toString();
            try {
                this.namedParameterJdbcTemplate.update(sql, createSqlParameterSource(t2));
            } catch (Throwable e) {
                throw new Exception(e);
            }
        }
    }

    @Override
    public void updateField(Serializable pk, String field, Object value)
            throws Exception {
        this.updateField(pk, new String[] { field }, new Object[] { value });
    }

    @Override
    public void updateField(Serializable pk, String[] fields, Object[] values)
            throws Exception {
        if (pk == null) {
            throw new IllegalArgumentException("primary key cannot be null.");
        }

        if (ArrayUtils.isEmpty(fields) || ArrayUtils.isEmpty(values)) {
            throw new IllegalArgumentException("fields and values cannot be empty.");
        }

        if (fields.length != values.length) {
            throw new IllegalArgumentException("length of fields must be equal with values.");
        }

        StringBuffer sb = new StringBuffer();
        sb.append(CONST_UPDATE).append(this.metadata.getQualifiedTableName()).append(" set ");
        for (int i = 0, len = fields.length; i < len; i++) {
            String field = fields[i];
            if (StringUtils.isBlank(field)) {
                throw new IllegalArgumentException("field name cannot be blank.");
            }
            sb.append(this.metadata.getColumnByField(field).getNameWithQuote()).append("=?");
            if (i != len - 1) {
                sb.append(",");
            }
        }
        sb.append(CONST_WHERE).append(this.metadata.getPrimaryKey().getNameWithQuote()).append("=?");

        final String sql = sb.toString();

        Object[] values2 = new Object[values.length + 1];
        for (int i = 0, len = values.length; i < len; i++) {
            values2[i] = values[i];
        }
        values2[values2.length - 1] = pk;
        try {
            this.jdbcTemplate.update(sql, values2);
        } catch (Throwable e) {
            throw new Exception(e);
        }
    }

    /**
     * 获取实体类主键值
     * 
     * @param t
     * @return
     * @throws Exception
     */
    private Serializable getPrimaryValue(T t) throws Exception {
        final EntityColumnMetadata pkMeta = this.metadata.getPrimaryKey();
        final String pk = StringUtils.capitalize(pkMeta.getField());
        Method md;
        try {
            String methodName = (pkMeta.getFieldType().equals(boolean.class) ? "is" : "get") + pk;
            md = t.getClass().getMethod(methodName);
            Serializable value = (Serializable) md.invoke(t);
            return value;
        } catch (Throwable e) {
            e.printStackTrace();
            throw new Exception("get primary value for " + t + " failed:", e);
        }
    }

    /**
     * 排序解析
     * 
     * @param sb
     * @param orderBy
     */
    private void parseOrder(final StringBuffer sb, final OrderBy... orderBy) {
        if (ArrayUtils.isNotEmpty(orderBy)) {
            sb.append(CONST_ORDER_BY);
            for (int i = 0, len = orderBy.length; i < len; i++) {
                sb.append(this.metadata.getColumnByField(orderBy[i].getField()).getNameWithQuote())
                        .append(CONST_SPACE)
                        .append(orderBy[i].isAsc() ? CONST_ORDER_BY_ASC : CONST_ORDER_BY_DESC);
                if (i != len - 1) {
                    sb.append(",");
                }
            }
        }
    }

    private void parseQueryContext(final StringBuffer sb,
            final QueryContext context, final List<Object> params)
            throws Exception {
        parseQueryContext(sb, context, params, null);
    }

    private void parseQueryContext(final StringBuffer sb,
            final QueryContext context, final List<Object> params,
            String tableAlias) throws Exception {
        if (context == null) {
            return;
        }

        boolean isFirst = true;

        for (Iterator<QueryContextPiece> it = context.iterator(); it.hasNext();) {
            final QueryContextPiece piece = it.next();

            // 过滤值为空的属性
            if (ArrayUtils.isEmpty(piece.getValues())) {
                continue;
            }
            if (piece.getValues()[0] == null || StringUtils.isEmpty(String.valueOf(piece.getValues()[0]))) {
                continue;
            }

            for (Object tmpV : piece.getValues()) {
                if (tmpV.getClass().isArray()) {
                    throw new Exception("illegal args: if you're using int[],long[] and other arrays,replaced them use Integer[],Long[]...");
                } else if (tmpV instanceof Collection<?>) {
                    throw new Exception("illegal args. Collection<E> cannot be used as query args!");
                }
            }

            boolean isIn = piece.getOperator().equals(IN.singleton) || piece.getOperator().equals(NOT_IN.singleton);
            boolean isBetween = piece.getOperator().equals(BETWEEN.singleton) || piece.getOperator().equals(NOT_BETWEEN.singleton);

            sb.append(CONST_SPACE).append(isFirst ? CONST_AND : piece.getAppendType().name()).append(CONST_SPACE);
            if (isIn && ArrayUtils.isEmpty(piece.getValues())) {
                sb.append(" 1=2 ");
                continue;
            }

            if (StringUtils.isNotBlank(tableAlias)) {
                sb.append(tableAlias).append(".");
            }

            sb.append("`").append(this.metadata.getColumnByField(piece.getField()).getName()).append("` ").append(piece.getOperator().value()).append(CONST_SPACE);
            isFirst = false;

            if (isIn) {
                sb.append(" (");
            }

            if (!isBetween) {
                if (piece.getValues().length < 1) {
                    throw new RuntimeException("where case error:'in' list cannot be empty!");
                }
                for (int i = 0, len = piece.getValues().length; i < len; i++) {
                    sb.append("?");
                    params.add(piece.getValues()[i]);
                    sb.append((i != len - 1) ? "," : " ");
                }
            } else if (piece.getValues().length == 2) {
                sb.append(" ? and ? ");
                params.add(piece.getValues()[0]);
                params.add(piece.getValues()[1]);
            } else {
                throw new Exception("operator between parameter size illegal.");
            }

            if (isIn) {
                sb.append(") ");
            }
        }
    }
}
